/**
 * 下午1:34:44
 */
package com.hsnn.medstgmini.pro.project.dao.impl;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

import javax.annotation.Resource;

import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.transform.Transformers;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.hsnn.medstgmini.dao.HibernateSqlUtil;
import com.hsnn.medstgmini.pro.project.dao.BidProjproddrugDao;
import com.hsnn.medstgmini.util.Pagination;

/**
 * @author hsnn
 *
 */
@Repository
public class BidProjproddrugDaoImpl implements BidProjproddrugDao {
	@Resource(name = "sessionFactory")
	private SessionFactory sessionFactory;
	
	@Autowired
	private HibernateSqlUtil dao;
	private Session getSession() {
		Session session = sessionFactory.getCurrentSession();
		if (null == session) {
			session = sessionFactory.openSession();
		}
		return session;
	}
	
	@Override
	public Pagination getBidProjectListByExpert(Pagination page) {
		//String expertId = page.getConditions().get("expertId");
		StringBuffer sql = new StringBuffer()
		.append(" select bp.proj_id projId, ")
		.append(" bp.proj_code projCode, ")
		.append(" bp.name, ")
		.append(" bp.starttime, ")
		.append(" bp.endtime ")
		.append(" from bid_projexpert bpe left join bid_project bp on bpe.proj_id = bp.proj_id  ")
		.append(" where bpe.status = 1 ")
		.append(" and bp.status = 1 ")
		.append(" and bpe.expert_id = :expertId  ");
		return dao.getSqlPageMapList(sql.toString(), page.getConditions(), page);
	}

	
	@Override
	public Pagination getBidProjproddrug(Pagination page) {
		//String projId = page.getConditions().get("projId");
		StringBuffer sql = new StringBuffer()
		.append(" SELECT bpd.proj_proddrug_id projProddrugId, ")
		.append("        a.drug_code          drugCode, ")
		.append("        b.qualityspec_name   qualityspecName, ")
		.append("        b.form, ")
		.append("        b.spec, ")
		.append("        a.factor, ")
		.append("        a.unit, ")
		.append("        d.material_name      materialName, ")
		.append("        c.comp_id      prodCompCode, ")
		.append("        c.comp_name          prodCompName ")
		.append(" FROM   bid_projproddrug bpd ")
		.append("        LEFT JOIN (SELECT bid_projdrugs_score_id, ")
		.append("                          bid_projdrugs_id ")
		.append("                   FROM   bid_projdrugs_score) bpsF ")
		.append("               ON bpsF.bid_projdrugs_id = bpd.proj_proddrug_id ")
		.append("        LEFT JOIN std_proddruginfo a ")
		.append("               ON a.prod_id = bpd.prod_id ")
		.append("        JOIN std_drugqualityspec b ")
		.append("          ON a.qualityspec_id = b.qualityspec_id ")
		.append("        LEFT JOIN std_compinfo c ")
		.append("               ON a.prod_comp_id = c.comp_id ")
		.append("        LEFT JOIN dic_drugmaterial d ")
		.append("               ON a.material_id = d.material_id ")
		.append(" WHERE  bpsF.bid_projdrugs_id is null ")
		.append("        AND ( bpd.audit_status = 1 ")
		.append("               OR ( bpd.audit_status = 0 ")
		.append("                    AND bpd.complain_status = 1 ) ) ")
		.append("    and  bpd.proj_id=:projId   ");
		return dao.getSqlPageMapList(sql.toString(), page.getConditions(), page);
	}

	
	@Override
	public Pagination getBidScoreRule(Pagination page) {
		
		String projId = page.getConditions().get("projId");
				//查列头
				String sqlTitle = "SELECT c.subject_score_code,c.subject_score_name  FROM  bid_score_rule a join dic_subject_score b on a.dic_subject_score_id = b.dic_subject_score_id join dic_subject_score c  on c.dic_subject_score_id = b.father_id where a.scor_type=2 and a.proj_id= :projId group by c.subject_score_name desc";
				Query queryTitle =  getSession().createSQLQuery(sqlTitle);
				queryTitle.setParameter("projId", projId);
				queryTitle.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
				List<String> titleList  = new ArrayList<String>();//表格列名称
				List<List<String>> list  = new ArrayList<List<String>>();
				@SuppressWarnings("unchecked")
				List<HashMap<String,String>> lists = (List<HashMap<String,String>>) queryTitle.list();
				for(int i=0;i<lists.size();i++ ){
					titleList.add(lists.get(i).get("subject_score_name"));
					List<String> minList = getSocre(lists.get(i).get("subject_score_code"));
					list.add(i,minList);
				}
				page.setForm(titleList);
				page.setRows(list);
				
		return page;
	}
	
	private List<String> getSocre(String code){
		StringBuffer A =new StringBuffer()
		.append(" select bsr.bid_score_rule_id id, ")
		.append(" temp.subject_score_name name, ")
		.append(" bsr.score, ")
		.append(" temp.father_id fatherId ")
		.append(" from (select  b.dic_subject_score_id ,b.subject_score_name,b.father_id  from dic_subject_score a left join dic_subject_score b ")
		.append(" on a.dic_subject_score_id = b.father_id ")
		.append(" where a.subject_score_code = :code ) temp  ")
		.append(" inner join bid_score_rule bsr  ")
		.append(" on  bsr.dic_subject_score_id = temp.dic_subject_score_id order by bsr.score desc ");//
		Query query =  getSession().createSQLQuery(A.toString());
		query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		query.setParameter("code", code);
		return query.list();
	}

}
